
using System;
using lo=unoidl.com.sun.star;
namespace ConsoleApplication1
{

  // __________  implementation  ____________________________________

  /** This is a helper class for the spreadsheet and table samples.
      It connects to a running office and creates a spreadsheet document.
      Additionally it contains various helper functions.
   */
  public class SpreadsheetDocHelper : System.IDisposable
  {

    // __  private members  ___________________________________________

    private const String msDataSheetName  = "Data";

    private lo.uno.XComponentContext m_xContext;
    private lo.lang.XMultiServiceFactory mxMSFactory;
    private lo.sheet.XSpreadsheetDocument mxDocument;

    // ________________________________________________________________

    public SpreadsheetDocHelper(String[] args)
    {
      // Connect to a running office and get the service manager
      mxMSFactory = connect(args);
      // Create a new spreadsheet document
      mxDocument = initDocument();
    }

    // __  helper methods  ____________________________________________

    /** Returns the service manager.
        @return  XMultiServiceFactory interface of the service manager. */
    public lo.lang.XMultiServiceFactory getServiceManager()
    {
      return mxMSFactory;
    }

    /** Returns the whole spreadsheet document.
        @return  XSpreadsheetDocument interface of the document. */
    public lo.sheet.XSpreadsheetDocument getDocument()
    {
      return mxDocument;
    }

    /** Returns the spreadsheet with the specified index (0-based).
        @param nIndex  The index of the sheet.
        @return  XSpreadsheet interface of the sheet. */
    public lo.sheet.XSpreadsheet getSpreadsheet(int nIndex)
    {
      // Collection of sheets
      lo.sheet.XSpreadsheets xSheets =
            mxDocument.getSheets();

      lo.container.XIndexAccess xSheetsIA =
            (lo.container.XIndexAccess) xSheets;

      lo.sheet.XSpreadsheet xSheet =
            (lo.sheet.XSpreadsheet)
            xSheetsIA.getByIndex(nIndex).Value;

      return xSheet;
    }

    /** Inserts a new empty spreadsheet with the specified name.
        @param aName  The name of the new sheet.
        @param nIndex  The insertion index.
        @return  The XSpreadsheet interface of the new sheet. */
    public lo.sheet.XSpreadsheet insertSpreadsheet(
        String aName, short nIndex)
    {
      // Collection of sheets
      lo.sheet.XSpreadsheets xSheets =
            mxDocument.getSheets();

      xSheets.insertNewByName(aName, nIndex);
      lo.sheet.XSpreadsheet xSheet =
            (lo.sheet.XSpreadsheet)
            xSheets.getByName(aName).Value;

      return xSheet;
    }

    // ________________________________________________________________
    // Methods to fill values into cells.

    /** Writes a double value into a spreadsheet.
        @param xSheet  The XSpreadsheet interface of the spreadsheet.
        @param aCellName  The address of the cell (or a named range).
        @param fValue  The value to write into the cell. */
    public void setValue(
        lo.sheet.XSpreadsheet xSheet,
        String aCellName,
        double fValue)
    {
      xSheet.getCellRangeByName(aCellName).getCellByPosition(
          0, 0).setValue(fValue);
    }

    /** Writes a formula into a spreadsheet.
        @param xSheet  The XSpreadsheet interface of the spreadsheet.
        @param aCellName  The address of the cell (or a named range).
        @param aFormula  The formula to write into the cell. */
    public void setFormula(
        lo.sheet.XSpreadsheet xSheet,
        String aCellName,
        String aFormula)
    {
      xSheet.getCellRangeByName(aCellName).getCellByPosition(
          0, 0).setFormula(aFormula);
    }

    /** Writes a date with standard date format into a spreadsheet.
        @param xSheet  The XSpreadsheet interface of the spreadsheet.
        @param aCellName  The address of the cell (or a named range).
        @param nDay  The day of the date.
        @param nMonth  The month of the date.
        @param nYear  The year of the date. */
    public void setDate(
        lo.sheet.XSpreadsheet xSheet,
        String aCellName,
        int nDay, int nMonth, int nYear)
    {
      // Set the date value.
      lo.table.XCell xCell =
            xSheet.getCellRangeByName(aCellName).getCellByPosition(0, 0);
      String aDateStr = nMonth + "/" + nDay + "/" + nYear;
      xCell.setFormula(aDateStr);

      // Set standard date format.
      lo.util.XNumberFormatsSupplier xFormatsSupplier =
            (lo.util.XNumberFormatsSupplier) getDocument();
      lo.util.XNumberFormatTypes xFormatTypes =
            (lo.util.XNumberFormatTypes)
            xFormatsSupplier.getNumberFormats();
      int nFormat = xFormatTypes.getStandardFormat(
          lo.util.NumberFormat.DATE,
          new lo.lang.Locale());

      lo.beans.XPropertySet xPropSet =
            (lo.beans.XPropertySet) xCell;
      xPropSet.setPropertyValue(
          "NumberFormat",
          new uno.Any((Int32) nFormat));
    }

    /** Draws a colored border around the range and writes the headline
        in the first cell.
        
        @param xSheet  The XSpreadsheet interface of the spreadsheet.
        @param aRange  The address of the cell range (or a named range).
        @param aHeadline  The headline text. */
    public void prepareRange(
        lo.sheet.XSpreadsheet xSheet,
        String aRange, String aHeadline)
    {
      lo.beans.XPropertySet xPropSet = null;
      lo.table.XCellRange xCellRange = null;

      // draw border
      xCellRange = xSheet.getCellRangeByName(aRange);
      xPropSet = (lo.beans.XPropertySet) xCellRange;
      lo.table.BorderLine aLine =
            new lo.table.BorderLine();
      aLine.Color = 0x99CCFF;
      aLine.InnerLineWidth = aLine.LineDistance = 0;
      aLine.OuterLineWidth = 100;
      lo.table.TableBorder aBorder =
            new lo.table.TableBorder();
      aBorder.TopLine = aBorder.BottomLine = aBorder.LeftLine =
            aBorder.RightLine = aLine;
      aBorder.IsTopLineValid = aBorder.IsBottomLineValid = true;
      aBorder.IsLeftLineValid = aBorder.IsRightLineValid = true;
      xPropSet.setPropertyValue(
          "TableBorder",
          new uno.Any(
              typeof(lo.table.TableBorder), aBorder));

      // draw headline
      lo.sheet.XCellRangeAddressable xAddr =
            (lo.sheet.XCellRangeAddressable) xCellRange;
      lo.table.CellRangeAddress aAddr =
            xAddr.getRangeAddress();

      xCellRange = xSheet.getCellRangeByPosition(
          aAddr.StartColumn,
          aAddr.StartRow, aAddr.EndColumn, aAddr.StartRow);

      xPropSet = (lo.beans.XPropertySet) xCellRange;
      xPropSet.setPropertyValue(
          "CellBackColor", new uno.Any((Int32) 0x99CCFF));
      // write headline
      lo.table.XCell xCell =
            xCellRange.getCellByPosition(0, 0);
      xCell.setFormula(aHeadline);
      xPropSet = (lo.beans.XPropertySet) xCell;
      xPropSet.setPropertyValue(
          "CharColor", new uno.Any((Int32) 0x003399));
      xPropSet.setPropertyValue(
          "CharWeight",
          new uno.Any((Single) lo.awt.FontWeight.BOLD));
    }

    // ________________________________________________________________
    // Methods to create cell addresses and range addresses.

    /** Creates a lo.table.CellAddress and initializes it
        with the given range.
        @param xSheet  The XSpreadsheet interface of the spreadsheet.
        @param aCell  The address of the cell (or a named cell). */
    public lo.table.CellAddress createCellAddress(
        lo.sheet.XSpreadsheet xSheet,
        String aCell)
    {
      lo.sheet.XCellAddressable xAddr =
        (lo.sheet.XCellAddressable)
          xSheet.getCellRangeByName(aCell).getCellByPosition(0, 0);
      return xAddr.getCellAddress();
    }

    /** Creates a lo.table.CellRangeAddress and initializes
        it with the given range.
        @param xSheet  The XSpreadsheet interface of the spreadsheet.
        @param aRange  The address of the cell range (or a named range). */
    public lo.table.CellRangeAddress createCellRangeAddress(
        lo.sheet.XSpreadsheet xSheet, String aRange)
    {
      lo.sheet.XCellRangeAddressable xAddr =
            (lo.sheet.XCellRangeAddressable)
          xSheet.getCellRangeByName(aRange);
      return xAddr.getRangeAddress();
    }

    // ________________________________________________________________
    // Methods to convert cell addresses and range addresses to strings.

    /** Returns the text address of the cell.
        @param nColumn  The column index.
        @param nRow  The row index.
        @return  A string containing the cell address. */
    public String getCellAddressString(int nColumn, int nRow)
    {
      String aStr = "";
      if( nColumn > 25 )
        aStr += (char) ( 'A' + nColumn / 26 - 1 );
      aStr += (char) ( 'A' + nColumn % 26 );
      aStr += ( nRow + 1 );
      return aStr;
    }

    /** Returns the text address of the cell range.
        @param aCellRange  The cell range address.
        @return  A string containing the cell range address. */
    public String getCellRangeAddressString(
        lo.table.CellRangeAddress aCellRange)
    {
      return
          getCellAddressString(aCellRange.StartColumn, aCellRange.StartRow)
            + ":"
            + getCellAddressString(aCellRange.EndColumn, aCellRange.EndRow);
    }

    /** Returns the text address of the cell range.
        @param xCellRange  The XSheetCellRange interface of the cell range.
        @param bWithSheet  true = Include sheet name.
        @return  A string containing the cell range address. */
    public String getCellRangeAddressString(
        lo.sheet.XSheetCellRange xCellRange, bool bWithSheet)
    {
      String aStr = "";
      if( bWithSheet )
      {
        lo.sheet.XSpreadsheet xSheet =
                xCellRange.getSpreadsheet();
        lo.container.XNamed xNamed =
                (lo.container.XNamed) xSheet;
        aStr += xNamed.getName() + ".";
      }
      lo.sheet.XCellRangeAddressable xAddr =
            (lo.sheet.XCellRangeAddressable) xCellRange;
      aStr += getCellRangeAddressString(xAddr.getRangeAddress());
      return aStr;
    }

    /** Returns a list of addresses of all cell ranges contained in the
        collection.
        
        @param xRangesIA  The XIndexAccess interface of the collection.
        @return  A string containing the cell range address list. */
    public String getCellRangeListString(
        lo.container.XIndexAccess xRangesIA)
    {
      String aStr = "";
      int nCount = xRangesIA.getCount();
      for( int nIndex = 0 ; nIndex < nCount ; ++nIndex )
      {
        if( nIndex > 0 )
          aStr += " ";
        uno.Any aRangeObj = xRangesIA.getByIndex(nIndex);
        lo.sheet.XSheetCellRange xCellRange =
                (lo.sheet.XSheetCellRange) aRangeObj.Value;
        aStr += getCellRangeAddressString(xCellRange, false);
      }
      return aStr;
    }

    // ________________________________________________________________

    /** Connect to a running office that is accepting connections.
        @return  The ServiceManager to instantiate office components. */
    private lo.lang.XMultiServiceFactory connect(String[] args)
    {

      m_xContext = uno.util.Bootstrap.bootstrap();

      return (lo.lang.XMultiServiceFactory) m_xContext.getServiceManager();
    }

    public void Dispose()
    {

    }

    /** Creates an empty spreadsheet document.
        @return  The XSpreadsheetDocument interface of the document. */
    private lo.sheet.XSpreadsheetDocument initDocument()
    {
      lo.frame.XComponentLoader aLoader = (lo.frame.XComponentLoader)
          mxMSFactory.createInstance("com.sun.star.frame.Desktop");

      lo.lang.XComponent xComponent = aLoader.loadComponentFromURL(
          "private:factory/scalc", "_blank", 0,
          new lo.beans.PropertyValue[0]);

      return (lo.sheet.XSpreadsheetDocument) xComponent;
    }

    // ________________________________________________________________
  }
}
